using System;
using System.Collections;
using System.IO;
using System.Windows.Forms;
using System.Xml;
using ICSharpCode.SharpZipLib.Zip;
using Excel = Microsoft.Office.Interop.Excel;

namespace CRMDocumentation
{
    class ExcelInteractionHelper
    {
        private static Object missing = Type.Missing;
        private XmlDocument xdCustomizations;
        public System.Collections.Specialized.StringCollection scEntities = new System.Collections.Specialized.StringCollection();
        public string strEntityLocalizedName;
        public string strLanguageCode;

        public void LoadXml()
        {
            try
            {
                //load the exported customizations!
                XmlDocument _xd = new XmlDocument();

                OpenFileDialog openFileDialog1 = new OpenFileDialog();
                openFileDialog1.Filter = "CRM 4.0 Configuration Export (*.xml,*.zip)|*.xml;*.zip";
                openFileDialog1.CheckFileExists = true;
                openFileDialog1.CheckPathExists = true;
                openFileDialog1.Multiselect = false;
                openFileDialog1.ShowDialog();

                if (openFileDialog1.FileName.EndsWith(".xml", true, null))
                {
                    _xd.Load(openFileDialog1.FileName);
                }
                else //Get the customization.xml file from the zip file
                {
                    MemoryStream zipMemoryStream = new MemoryStream();

                    ZipInputStream s = new ZipInputStream(File.OpenRead(openFileDialog1.FileName));

                    ZipEntry theEntry;
                    while ((theEntry = s.GetNextEntry()) != null)
                    {
                        string fileName = Path.GetFileName(theEntry.Name);
                        //skip any files other than the standard named xml file in the zip
                        if (fileName == "customizations.xml")
                        {
                            int size = 2048;
                            byte[] data = new byte[2048];
                            while (true)
                            {
                                size = s.Read(data, 0, data.Length);
                                if (size > 0)
                                {
                                    zipMemoryStream.Write(data, 0, size);
                                }
                                else
                                {
                                    //we've reached the end of the file, move the pointer back to the beginning
                                    zipMemoryStream.Position = 0;

                                    //and then load this into the temporary document.
                                    _xd.Load(zipMemoryStream);

                                    break;
                                }
                            }

                        }
                    }
                }
                //set the public xdCustomizations document equal to the loaded XML file.
                if (_xd != null)
                {
                    //Validate, then load the xml document.
                    if (_xd.SelectSingleNode("/ImportExportXml/@version") != null)
                    {
                        if (_xd.SelectSingleNode("/ImportExportXml/@version").InnerText == "4.0.0.0")
                            xdCustomizations = _xd;
                        else
                            MessageBox.Show("This does not appear to be a CRM 4.0.0.0 Export, your results may vary.");
                        xdCustomizations = _xd;
                    }
                    else
                        MessageBox.Show("This does not appear to be a valid CRM Export, please try another file.");
                }
            
                if (xdCustomizations != null)
                {
                    CRMDocumentation.Properties.Settings mySettings = new CRMDocumentation.Properties.Settings();
                    strLanguageCode = xdCustomizations.SelectSingleNode("/ImportExportXml/@languagecode").InnerText;
                    if (mySettings.AllEntitites)
                    {
                        foreach (XmlNode xnForm in xdCustomizations.SelectNodes("ImportExportXml/Entities/Entity/FormXml[../Name]"))
                        {
                            string strEntityName = xnForm.SelectSingleNode("../Name").InnerText;
                            scEntities.Add(strEntityName);
                        }
                    }
                    else
                    {
                        scEntities = mySettings.Entities;
                    }
                 }

                 ArrayList myArrayList = ArrayList.Adapter(scEntities);
                 myArrayList.Sort();

            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occurred " + ex.Message);
            }


        }

        public void DocForms(Excel.Application thisApp)
        {
            try
            {
                Excel.Range myRange;
                foreach (string strEntityName in scEntities)
                    {
                        //get the entity
                        XmlNode xnEntity = xdCustomizations.SelectSingleNode("ImportExportXml/Entities/Entity[Name='" + strEntityName + "']");
                        string strWorksheetName = strEntityName.ToString();
                        strWorksheetName = Left(strWorksheetName, 20) + "-Form";

                        if (xnEntity != null && !SheetExists(thisApp, strWorksheetName))
                        {
                            string strObjectTypeCode = xnEntity.SelectSingleNode("ObjectTypeCode").InnerText;
                            strEntityLocalizedName = xnEntity.SelectSingleNode("EntityInfo/entity/LocalizedNames/LocalizedName[@languagecode='" + strLanguageCode + "']/@description").InnerText;
                            CreateSheet(Globals.ThisAddIn.Application, strWorksheetName);

                            //get the forms
                            foreach (XmlNode xnForm in xnEntity.SelectNodes("FormXml/forms/entity[form/@type='main']/form"))
                            {
                                string strFormType = xnForm.Attributes["type"].Value;
                                WriteFormFormHeader(thisApp, strEntityLocalizedName, strEntityName, strObjectTypeCode);

                                //get the tabs
                                foreach (XmlNode xnTab in xnForm.SelectNodes("tabs/tab"))
                                {
                                    string strTabName = xnTab.SelectSingleNode("labels/label/@description[1]").InnerText;

                                    WriteFormTabHeader(thisApp, strTabName);

                                    //get the sections
                                    foreach (XmlNode xnSection in xnTab.SelectNodes("sections/section"))
                                    {
                                        string strSectionName = xnSection.SelectSingleNode("labels/label/@description").InnerText;
                                        string strSectionShowLabel = xnSection.SelectSingleNode("@showlabel").InnerText;

                                        WriteFormSectionHeader(thisApp, strSectionName, strSectionShowLabel);
                                        thisApp.ScreenUpdating = true;
                                        thisApp.ScreenUpdating = false;
                                        WriteFormRowHeader(thisApp);

                                        //get the rows
                                        foreach (XmlNode xnRow in xnSection.SelectNodes("rows/row[cell]"))
                                        {
                                            //get the cells
                                            foreach (XmlNode xnCell in xnRow.SelectNodes("cell[control]"))
                                            {
                                                string strFieldName = "";
                                                string strLabelOnForm = "";
                                                string strLabelOnAdvancedFind = "";
                                                string strDescription = "";
                                                string strDisabled = "";
                                                string strType = "";
                                                string strLength = "";
                                                string strRequiredLevel = "";
                                                string strMaxLength = "";
                                                string strFormat = "";
                                                string strMinValue = "";
                                                string strMaxValue = "";
                                                string strAccuracy = "";
                                                string strDefaultValue = "";

                                                if (xnCell.SelectSingleNode("control/@datafieldname") != null)
                                                    strFieldName = xnCell.SelectSingleNode("control/@datafieldname").InnerText;
                                                if (xnCell.SelectSingleNode("labels/label/@description") != null)
                                                    strLabelOnForm = xnCell.SelectSingleNode("labels/label/@description").InnerText;
                                                if (xnCell.SelectSingleNode("control/@disabled") != null)
                                                    strDisabled = xnCell.SelectSingleNode("control/@disabled").InnerText;


                                                //get info from EntityInfo/Attributes for regular attributes
                                                if (xnEntity.SelectSingleNode("EntityInfo/entity/attributes/attribute[translate(@PhysicalName, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz') = '" + strFieldName.ToLower() + "']") != null)
                                                {
                                                    XmlNode xnFieldXml = xnEntity.SelectSingleNode("EntityInfo/entity/attributes/attribute[translate(@PhysicalName, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz') = '" + strFieldName.ToLower() + "']");
                                                    if (xnFieldXml.SelectSingleNode("displaynames/displayname/@description") != null)
                                                        strLabelOnAdvancedFind = xnFieldXml.SelectSingleNode("displaynames/displayname/@description").InnerText;
                                                    if (xnFieldXml.SelectSingleNode("Descriptions/Description[@languagecode='" + strLanguageCode + "']/@description") != null)
                                                        strDescription = xnFieldXml.SelectSingleNode("Descriptions/Description[@languagecode='" + strLanguageCode + "']/@description").InnerText;
                                                    if (xnFieldXml.SelectSingleNode("RequiredLevel") != null)
                                                        strRequiredLevel = xnFieldXml.SelectSingleNode("RequiredLevel").InnerText;
                                                    if (xnFieldXml.SelectSingleNode("Type") != null)
                                                        strType = xnFieldXml.SelectSingleNode("Type").InnerText;
                                                    if (xnFieldXml.SelectSingleNode("Length") != null)
                                                        strLength = xnFieldXml.SelectSingleNode("Length").InnerText;
                                                    if (xnFieldXml.SelectSingleNode("MaxLength") != null)
                                                        strMaxLength = xnFieldXml.SelectSingleNode("MaxLength").InnerText;
                                                    if (xnFieldXml.SelectSingleNode("Format") != null)
                                                        strFormat = xnFieldXml.SelectSingleNode("Format").InnerText;
                                                    if (xnFieldXml.SelectSingleNode("MinValue") != null)
                                                        strMinValue = xnFieldXml.SelectSingleNode("MinValue").InnerText;
                                                    if (xnFieldXml.SelectSingleNode("MaxValue") != null)
                                                        strMaxValue = xnFieldXml.SelectSingleNode("MaxValue").InnerText;
                                                    if (xnFieldXml.SelectSingleNode("Accuracy") != null)
                                                        strAccuracy = xnFieldXml.SelectSingleNode("Accuracy").InnerText;
                                                    if (xnFieldXml.SelectSingleNode("DefaultValue") != null)
                                                        strDefaultValue = xnFieldXml.SelectSingleNode("DefaultValue").InnerText;
                                                }

                                                //get info from EntityRelationships
                                                if (xdCustomizations.SelectSingleNode("ImportExportXml/EntityRelationships/EntityRelationship[translate(ReferencingAttributeName, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz') ='" + strFieldName.ToLower() + "']") != null)
                                                {
                                                    XmlNode xnLookupXml = xdCustomizations.SelectSingleNode("ImportExportXml/EntityRelationships/EntityRelationship[translate(ReferencingAttributeName, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz') ='" + strFieldName.ToLower() + "']");
                                                    if (xnLookupXml.SelectSingleNode("field/displaynames/displayname/@description") != null)
                                                        strLabelOnAdvancedFind = xnLookupXml.SelectSingleNode("field/displaynames/displayname/@description").InnerText;
                                                    if (xnLookupXml.SelectSingleNode("RelationshipDescription/Descriptions/Description/@description") != null)
                                                        strDescription = xnLookupXml.SelectSingleNode("RelationshipDescription/Descriptions/Description/@description").InnerText;
                                                    if (xnLookupXml.SelectSingleNode("field/@requiredlevel") != null)
                                                        strRequiredLevel = xnLookupXml.SelectSingleNode("field/@requiredlevel").InnerText;
                                                    strType = "Lookup";
                                                }


                                                myRange = thisApp.ActiveCell;

                                                if (strFieldName.ToString() == "")
                                                    strFieldName = "-";
                                                else
                                                    myRange.Value2 = strFieldName;
                                                myRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                                                myRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;

                                                if (strRequiredLevel == "required")
                                                {
                                                    myRange.Font.Color = -16776961;
                                                }
                                                if (strRequiredLevel == "recommended")
                                                {
                                                    myRange.Font.Color = -4165632;
                                                }
                                                myRange.get_Offset(0, 1).Value2 = strLabelOnForm;
                                                myRange.get_Offset(0, 1).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                                                myRange.get_Offset(0, 1).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;

                                                myRange.get_Offset(0, 2).Value2 = strLabelOnAdvancedFind;
                                                myRange.get_Offset(0, 2).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                                                myRange.get_Offset(0, 2).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;

                                                myRange.get_Offset(0, 3).Value2 = strDescription;
                                                myRange.get_Offset(0, 3).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                                                myRange.get_Offset(0, 3).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;

                                                switch (strType)
                                                {
                                                    case "nvarchar":
                                                        if (strMaxLength == "") strMaxLength = strLength;
                                                        strType = strType + " " + strFormat + " (" + strMaxLength + ")";
                                                        break;
                                                    case "int":
                                                        strType = strType + " (" + strMinValue + ", " + strMaxValue + ")";
                                                        break;
                                                    default:
                                                        break;
                                                }
                                                myRange.get_Offset(0, 4).Value2 = strType;
                                                myRange.get_Offset(0, 4).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                                                myRange.get_Offset(0, 4).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
                                                myRange.get_Offset(0, 4).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                                                myRange.get_Offset(0, 4).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
                                                myRange.get_Offset(1, 0).Activate();
                                            }
                                        }
                                    }
                                }
                                WriteFormFormFooter(thisApp);
                            }
                            FormatColumnWidth(thisApp);
                        }
                        FormatSheetLandscape(thisApp);
                    }
                    thisApp.ScreenUpdating = true;
                }
        
            catch (Exception ex)
            {
                MessageBox.Show("An error occurred- " + ex.Message);
                thisApp.ScreenUpdating = true;
            }
        }

        public void DocPicklists(Excel.Application thisApp)
        {
            try
            {
                if (xdCustomizations != null)
                {
 
                    foreach (string strEntityName in scEntities)
                    {
                        string strWorksheetName = Left(strEntityName.ToString(), 20) + "-Picklst";

                        //get the entity
                        XmlNode xnEntity = xdCustomizations.SelectSingleNode("ImportExportXml/Entities/Entity[Name='" + strEntityName + "']");

                        if (xnEntity != null && !SheetExists(thisApp, strWorksheetName))
                        {
                            string strObjectTypeCode = xnEntity.SelectSingleNode("ObjectTypeCode").InnerText;
                            string strEntityLocalizedName = xnEntity.SelectSingleNode("EntityInfo/entity/LocalizedNames/LocalizedName[@languagecode='" + strLanguageCode + "']/@description").InnerText;

                            // create a sheet for this entity picklist
                            CreateSheet(Globals.ThisAddIn.Application, strWorksheetName);

                            // activate next cell to the right for formatting                          
                            Excel.Range nextcell = (Excel.Range)thisApp.Cells[thisApp.ActiveCell.Row, thisApp.ActiveCell.Column + 1];
                            nextcell.Activate();

                            //get the forms
                            foreach (XmlNode xnForm in xnEntity.SelectNodes("FormXml/forms/entity[form/@type='main']/form"))
                            {
                                //get the tabs
                                foreach (XmlNode xnTab in xnForm.SelectNodes("tabs/tab"))
                                {
                                    //get the sections
                                    foreach (XmlNode xnSection in xnTab.SelectNodes("sections/section"))
                                    {
                                        //get the rows
                                        foreach (XmlNode xnRow in xnSection.SelectNodes("rows/row"))
                                        {
                                            //get the cells
                                            foreach (XmlNode xnCell in xnRow.SelectNodes("cell[control]"))
                                            {
                                                string strFieldName = "";
                                                string strLabelOnForm = "";
                                                string strDescription = "";
                                                string strRequiredLevel = "";
                                                string strDefaultOptionValue = "";


                                                if (xnCell.SelectSingleNode("control/@datafieldname") != null)
                                                    strFieldName = xnCell.SelectSingleNode("control/@datafieldname").InnerText;
                                                if (xnCell.SelectSingleNode("labels/label/@description") != null)
                                                    strLabelOnForm = xnCell.SelectSingleNode("labels/label/@description").InnerText;


                                                foreach (XmlNode xnPicklistField in xnEntity.SelectNodes("EntityInfo/entity/attributes/attribute[translate(@PhysicalName, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijklmnopqrstuvwxyz') = '" + strFieldName.ToLower() + "'][options]"))
                                                {
                                                    if ((xnPicklistField.SelectSingleNode("@PhysicalName") != null) && (xnPicklistField.SelectSingleNode("Type") != null))
                                                    {
                                                        if ((xnPicklistField.SelectSingleNode("@PhysicalName").InnerText.ToLower() == strFieldName.ToLower()) && (xnPicklistField.SelectSingleNode("Type").InnerText.ToLower() == "picklist"))
                                                        {

                                                            string strType = xnPicklistField.SelectSingleNode("Type").InnerText;
                                                            string strPhysicalName = xnPicklistField.SelectSingleNode("@PhysicalName").InnerText;

                                                            if (xnPicklistField.SelectSingleNode("Descriptions/description[@languagecode='" + strLanguageCode + "']/@description") != null)
                                                                strDescription = xnPicklistField.SelectSingleNode("Descriptions/description[@languagecode='" + strLanguageCode + "']/@description").InnerText;
                                                            // get the default value
                                                            if (xnPicklistField.SelectSingleNode("@AppDefaultValue") != null)
                                                                strDefaultOptionValue = xnPicklistField.SelectSingleNode("@AppDefaultValue").InnerText;
                                                            // get the requirement level
                                                            if (xnPicklistField.SelectSingleNode("@RequiredLevel") != null)
                                                                strRequiredLevel = xnPicklistField.SelectSingleNode("@RequiredLevel").InnerText;

                                                            //remember the first two cells
                                                            Excel.Range mergecell1 = (Excel.Range)thisApp.ActiveCell;
                                                            mergecell1 = mergecell1.get_Offset(0, -1);
                                                            Excel.Range mergecell2 = (Excel.Range)thisApp.ActiveCell;
                                                            Excel.Range mergecell3 = (Excel.Range)mergecell1.get_Offset(1, 0);
                                                            Excel.Range mergecell4 = (Excel.Range)mergecell2.get_Offset(1, 0);

                                                            // write the schema name
                                                            // write the label
                                                            thisApp.ActiveCell.Value2 = xnPicklistField.SelectSingleNode("displaynames/displayname[@languagecode='" + strLanguageCode + "']/@description").InnerText;
                                                            thisApp.ActiveCell.Font.Size = 12;
                                                            thisApp.ActiveCell.Font.Bold = true;
                                                            thisApp.ActiveCell.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                                                            thisApp.ActiveCell.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
                                                            thisApp.ActiveCell.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                                                            thisApp.ActiveCell.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
                                                            thisApp.ActiveCell.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
                                                            thisApp.ActiveCell.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
                                                            thisApp.ActiveCell.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                                                            thisApp.ActiveCell.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
                                                            thisApp.ActiveCell.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
                                                            thisApp.ActiveCell.Interior.PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
                                                            thisApp.ActiveCell.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorLight1;
                                                            thisApp.ActiveCell.Interior.TintAndShade = 0.599993896298105;
                                                            thisApp.ActiveCell.Interior.PatternTintAndShade = 0;
                                                            thisApp.ActiveCell.get_Offset(1, 0).Activate();

                                                            thisApp.ActiveCell.Value2 = "(" + strPhysicalName + ")";
                                                            thisApp.ActiveCell.Font.Size = 10;
                                                            thisApp.ActiveCell.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                                                            thisApp.ActiveCell.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
                                                            thisApp.ActiveCell.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                                                            thisApp.ActiveCell.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
                                                            thisApp.ActiveCell.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
                                                            thisApp.ActiveCell.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
                                                            thisApp.ActiveCell.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                                                            thisApp.ActiveCell.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
                                                            thisApp.ActiveCell.get_Offset(1, 0).Activate();


                                                            foreach (XmlNode xnPickListOption in xnPicklistField.SelectNodes("options/option"))
                                                            {
                                                                string strThisOptionValue = "";
                                                                if (xnPickListOption.SelectSingleNode("@value") != null)
                                                                    strThisOptionValue = xnPickListOption.SelectSingleNode("@value").InnerText;
                                                                Excel.Range leftcell = (Excel.Range)thisApp.Cells[thisApp.ActiveCell.Row, thisApp.ActiveCell.Column - 1];
                                                                leftcell.Value2 = strThisOptionValue;

                                                                thisApp.ActiveCell.Value2 = xnPickListOption.SelectSingleNode("labels/label/@description[1]").InnerText;
                                                                if (strThisOptionValue == strDefaultOptionValue)
                                                                    thisApp.ActiveCell.Font.Bold = true;
                                                                thisApp.ActiveCell.get_Offset(1, 0).Activate();
                                                            }

                                                            Excel.Worksheet mySheet2 = (Excel.Worksheet)thisApp.ActiveSheet;

                                                            // autofit
                                                            Excel.Range myRange3 = mySheet2.get_Range(thisApp.ActiveCell.get_Offset(0, -1), thisApp.ActiveCell);
                                                            myRange3.EntireColumn.AutoFit();

                                                            // merge the first two rows
                                                            Excel.Range myRange2 = (Excel.Range)thisApp.get_Range(mergecell1, mergecell2);
                                                            myRange2.Merge(true);
                                                            myRange2 = (Excel.Range)thisApp.get_Range(mergecell3, mergecell4);
                                                            myRange2.Merge(true);

                                                            // activate the next column for a new picklist
                                                            myRange3 = (Excel.Range)thisApp.Cells[1, thisApp.ActiveCell.Column + 3];
                                                            myRange3.Activate();

                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }

                        FormatSheetLandscape(thisApp);
                    }
                }
            }

            catch (Exception ex)
            {
                thisApp.ScreenUpdating = true;
                MessageBox.Show("An error occurred " + ex.Message);
            }
        }

        public void DocScripts(Excel.Application thisApp)
        {
            //Excel.Worksheet mySheet;

            if (xdCustomizations != null)
                try
                {
                    foreach (string strEntityName in scEntities)
                    {
                        string strWorksheetName = Left(strEntityName.ToString(), 20) + "-Script";
                        if (!SheetExists(thisApp, strWorksheetName))
                        {
                            // create a sheet for this entity
                            CreateSheet(Globals.ThisAddIn.Application, strWorksheetName);

                            Excel.Range myRange = thisApp.ActiveCell;
                            myRange.Value2 = "Level";
                            myRange.get_Offset(0, 1).Value2 = "Label";
                            myRange.get_Offset(0, 2).Value2 = "Attribute";
                            myRange.get_Offset(0, 3).Value2 = "Event";
                            myRange.get_Offset(0, 4).Value2 = "Enabled";
                            myRange.get_Offset(0, 5).Value2 = "Script";

                            // make first row bold
                            Excel.Range myRange2 = thisApp.get_Range(thisApp.ActiveCell, thisApp.ActiveCell.get_Offset(0, 5));
                            myRange2.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
                            myRange2.Interior.PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
                            myRange2.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorLight1;
                            myRange2.Interior.TintAndShade = 0.599993896298105;
                            myRange2.Interior.PatternTintAndShade = 0;
                            myRange2.Font.Bold = true;

                            myRange.get_Offset(1, 0).Activate();

                            //get the entity
                            XmlNode xnEntity = xdCustomizations.SelectSingleNode("ImportExportXml/Entities/Entity[Name='" + strEntityName + "']");

                            //get the forms
                            foreach (XmlNode xnForm in xnEntity.SelectNodes("FormXml/forms/entity[form/@type]"))
                            {
                                string strFormType = xnForm.SelectSingleNode("form/@type").InnerText;

                                //get the form events
                                foreach (XmlNode xnEvent in xnForm.SelectNodes("form/events/event"))
                                {
                                    string strFormOrField = "Form";
                                    string strLabel = "-";
                                    string strMetaname = "-";
                                    string strEventName = xnEvent.SelectSingleNode("@name").InnerText;
                                    string strIsEnabled = xnEvent.SelectSingleNode("@active").InnerText;
                                    string strScript = xnEvent.SelectSingleNode("script").InnerText;

                                    myRange = thisApp.ActiveCell;
                                    myRange.Value2 = strFormOrField;
                                    myRange.get_Offset(0, 1).Value2 = strLabel;
                                    myRange.get_Offset(0, 2).Value2 = strMetaname;
                                    myRange.get_Offset(0, 3).Value2 = strEventName;
                                    myRange.get_Offset(0, 4).Value2 = strIsEnabled;

                                    //replace tabs with spaces (tabs are not displayed right in Excel)
                                    System.Text.RegularExpressions.Regex MyRegex = new System.Text.RegularExpressions.Regex("\t");
                                    System.Text.RegularExpressions.Regex MyRegexAmp = new System.Text.RegularExpressions.Regex("&#38;");

                                    System.IO.StringReader sr = new System.IO.StringReader(strScript);
                                    while (true)
                                    {
                                        string strLine = sr.ReadLine();
                                        if (strLine != null)
                                        {
                                            strLine = MyRegex.Replace(strLine, "   ");
                                            strLine = MyRegexAmp.Replace(strLine, "&");

                                            thisApp.ActiveCell.get_Offset(0, 5).Value2 = strLine;
                                            thisApp.ActiveCell.get_Offset(1, 0).Activate();
                                        }
                                        else
                                            break;

                                    }
                                    //since this is the last row of the formula underline it.
                                    thisApp.ActiveCell.get_Offset(1, 0).Activate();
                                    myRange2 = thisApp.get_Range(thisApp.ActiveCell.get_Offset(-1, 0), thisApp.ActiveCell.get_Offset(-1, 5));
                                    myRange2.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                                    myRange2.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;

                                }

                                //get the field events
                                foreach (XmlNode xnEvent in xnForm.SelectNodes("form/tabs/tab/sections/section/rows/row/cell/events/event"))
                                {
                                    string strFormOrField = "Field";
                                    string strLabel = "";
                                    string strMetaName = "";
                                    string strIsEnabled = "";
                                    string strEventName = "onchange";
                                    string strScript = "";

                                    if (xnEvent.SelectSingleNode("../../labels/label/@description") != null)
                                        strLabel = xnEvent.SelectSingleNode("../../labels/label[1]/@description").InnerText;
                                    if (xnEvent.SelectSingleNode("../../control/@datafieldname") != null)
                                        strMetaName = xnEvent.SelectSingleNode("../../control/@datafieldname").InnerText;
                                    if (xnEvent.SelectSingleNode("@active") != null)
                                        strIsEnabled = xnEvent.SelectSingleNode("@active").InnerText;
                                    if (xnEvent.SelectSingleNode("script") != null)
                                        strScript = xnEvent.SelectSingleNode("script").InnerText;

                                    myRange = thisApp.ActiveCell;
                                    myRange.Value2 = strFormOrField;
                                    myRange.get_Offset(0, 1).Value2 = strLabel;
                                    myRange.get_Offset(0, 2).Value2 = strMetaName;
                                    myRange.get_Offset(0, 3).Value2 = strEventName;
                                    myRange.get_Offset(0, 4).Value2 = strIsEnabled;


                                    System.Text.RegularExpressions.Regex MyRegex = new System.Text.RegularExpressions.Regex("\t");
                                    System.Text.RegularExpressions.Regex MyRegexAmp = new System.Text.RegularExpressions.Regex("&#38;");

                                    System.IO.StringReader sr = new System.IO.StringReader(strScript);
                                    while (true)
                                    {
                                        string strLine = sr.ReadLine();

                                        if (strLine != null)
                                        {
                                            strLine = MyRegex.Replace(strLine, "   ");
                                            strLine = MyRegexAmp.Replace(strLine, "&");

                                            thisApp.ActiveCell.get_Offset(0, 5).Value2 = strLine;
                                            thisApp.ActiveCell.get_Offset(1, 0).Activate();
                                        }
                                        else
                                            break;
                                    }
                                    //since this is the last row of the formula underline it.
                                    thisApp.ActiveCell.get_Offset(1, 0).Activate();
                                    myRange2 = thisApp.get_Range(thisApp.ActiveCell.get_Offset(-1, 0), thisApp.ActiveCell.get_Offset(-1, 5));
                                    myRange2.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                                    myRange2.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;
                                }

                            }
                            Excel.Worksheet tempworksheet = (Excel.Worksheet)thisApp.ActiveSheet;

                            // make columns fit
                            myRange2 = tempworksheet.get_Range("A1", "A1");
                            myRange2.EntireColumn.AutoFit();
                            myRange2 = tempworksheet.get_Range("B1", "B1");
                            myRange2.EntireColumn.AutoFit();
                            myRange2 = tempworksheet.get_Range("C1", "C1");
                            myRange2.EntireColumn.AutoFit();
                            myRange2 = tempworksheet.get_Range("D1", "D1");
                            myRange2.EntireColumn.AutoFit();
                            myRange2 = tempworksheet.get_Range("E1", "E1");
                            myRange2.EntireColumn.AutoFit();
                            myRange2 = tempworksheet.get_Range("F1", "F1");
                            myRange2.EntireColumn.AutoFit();
                            FormatSheetLandscape(thisApp);

                        }
                    }
                }
                catch (Exception ex)
                {
                    thisApp.ScreenUpdating = true;
                    MessageBox.Show("An error occurred " + ex.Message);
                }

        }

        private void FormatSheetLandscape(Excel.Application thisApp)
        {
            try
            {
                Excel.Worksheet mySheet = (Excel.Worksheet)thisApp.ActiveSheet;
                try
                {
                    mySheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
                    mySheet.PageSetup.Zoom = false;
                    mySheet.PageSetup.FitToPagesTall = false;
                    mySheet.PageSetup.FitToPagesWide = 1;
                }
                catch (Exception ex)
                { 
                }
                ((Microsoft.Office.Interop.Excel._Worksheet)mySheet).Activate();
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occurred in CreateSheet() FormatSheetLandscape " + ex.Message);
                thisApp.ScreenUpdating = true;
            }

        }

        private void FormatColumnWidth(Excel.Application thisApp)
        {
            Excel.Worksheet mySheet = (Excel.Worksheet)thisApp.ActiveSheet;

            // activate the first cell
            Excel.Range myRange = mySheet.get_Range("A1", "XFD1048576");
            myRange.EntireColumn.AutoFit();
            myRange = mySheet.get_Range("A1", "A1");
            myRange.Activate();
        }

        private bool SheetExists(Excel.Application thisApp, string strName)
        {
            bool returnValue = false;
            try
            {

                foreach (Excel.Worksheet myWorksheet in thisApp.Worksheets)
                {
                    if (myWorksheet.Name == strName)
                    {
                        returnValue = true;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occurred in CreateSheet() SheetExists" + ex.Message);
                thisApp.ScreenUpdating = true;
            }
            return returnValue;
        }

        private void CreateSheet(Excel.Application thisApp, string strName)
        {
            // create a sheet for this entity
            // TODO: if the sheet exists, delete and insert at the same sheet.....
            try
            {
                Excel.Worksheet myExistingWorksheet = null;
                // if it exists just skip it
                foreach (Excel.Worksheet myWorksheet in thisApp.Worksheets)
                {
                    if (myWorksheet.Name == strName)
                    {
                        myExistingWorksheet = myWorksheet;
                        ((Microsoft.Office.Interop.Excel._Worksheet)myWorksheet).Activate();
                        //myWorksheet.Delete();
                        break;
                    }
                }
                if (myExistingWorksheet == null)
                {
                    Excel.Worksheet newWorksheet = (Excel.Worksheet)thisApp.ActiveWorkbook.Worksheets.Add(missing, thisApp.Worksheets[thisApp.Worksheets.Count], missing, missing);
                    newWorksheet.Name = strName;
                    ((Microsoft.Office.Interop.Excel._Worksheet)newWorksheet).Activate();

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occurred in CreateSheet() " + ex.Message);
                thisApp.ScreenUpdating = true;
            }
        }

        private void WriteFormRowHeader(Excel.Application thisApp)
        {
            try
            {
                //create the header
                Excel.Range myRange = thisApp.ActiveCell;

                myRange.Value2 = "Field";
                myRange.Font.Italic = true;
                myRange.Font.Size = 10;
                myRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                myRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
                myRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                myRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;

                myRange.get_Offset(0, 1).Value2 = "Label on form";
                myRange.get_Offset(0, 1).Font.Italic = true;
                myRange.get_Offset(0, 1).Font.Size = 10;
                myRange.get_Offset(0, 1).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                myRange.get_Offset(0, 1).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;

                myRange.get_Offset(0, 2).Value2 = "Label on Adv. Find";
                myRange.get_Offset(0, 2).Font.Italic = true;
                myRange.get_Offset(0, 2).Font.Size = 10;
                myRange.get_Offset(0, 2).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                myRange.get_Offset(0, 2).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;

                myRange.get_Offset(0, 3).Value2 = "Description";
                myRange.get_Offset(0, 3).Font.Italic = true;
                myRange.get_Offset(0, 3).Font.Size = 10;
                myRange.get_Offset(0, 3).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                myRange.get_Offset(0, 3).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;

                myRange.get_Offset(0, 4).Value2 = "Type";
                myRange.get_Offset(0, 4).Font.Italic = true;
                myRange.get_Offset(0, 4).Font.Size = 10;
                myRange.get_Offset(0, 4).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                myRange.get_Offset(0, 4).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;

                myRange.get_Offset(1, 0).Activate();
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occurred in WriteFormRowHeader() " + ex.Message);
                thisApp.ScreenUpdating = true;
            }
        }

        private void WriteFormFormHeader(Excel.Application thisApp, string strEntityLocalizedName, string strEntityName, string strObjectTypeCode)
        {
            try
            {
                //create the header 
                Excel.Range myRange = thisApp.ActiveCell;

                myRange.Value2 = "Entity: " + strEntityName + " \"" + strEntityLocalizedName + "\" (" + strObjectTypeCode + ")";
                myRange.Font.Size = 14;
                Excel.Range myMergeRange = thisApp.get_Range(thisApp.ActiveCell, thisApp.ActiveCell.get_Offset(0, 4));
                myMergeRange.Merge(missing);
                myRange.Font.Bold = true;

                myRange.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
                myRange.Interior.PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
                myRange.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorLight1;
                myRange.Interior.TintAndShade = 0.599993896298105;
                myRange.Interior.PatternTintAndShade = 0;

                myMergeRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                myMergeRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                myMergeRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                myMergeRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
                myRange.get_Offset(1, 0).Activate();
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occurred in WriteFormFormHeader() " + ex.Message);
                thisApp.ScreenUpdating = true;
            }
        }

        private void WriteFormSectionHeader(Excel.Application thisApp, string strSectionName, string strSectionShowLabel)
        {
            try
            {
                //create the labels for the section
                Excel.Range myRange = thisApp.ActiveCell;
                myRange.Value2 = "Section: " + strSectionName;
                if (strSectionShowLabel == "false")
                    myRange.Value2 = (string)myRange.Value2 + " (hidden)";
                Excel.Range myMergeRange = thisApp.get_Range(thisApp.ActiveCell, thisApp.ActiveCell.get_Offset(0, 4));
                myMergeRange.Merge(missing);
                myMergeRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                myMergeRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                myMergeRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                myMergeRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
                myRange.get_Offset(1, 0).Activate();
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occurred in WriteFormSectionHeader() " + ex.Message);
                thisApp.ScreenUpdating = true;
            }
        }

        private void WriteFormTabHeader(Excel.Application thisApp, string strTabName)
        {
            try
            {
                // writes a row for a tab page found in CRM
                Excel.Range myRange = thisApp.ActiveCell;
                myRange.Value2 = "Tab: " + strTabName;
                myRange.Font.Bold = true;

                // merge cells
                Excel.Range myMergeRange = thisApp.get_Range(thisApp.ActiveCell, thisApp.ActiveCell.get_Offset(0, 4));
                myMergeRange.Merge(missing);

                myMergeRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                myMergeRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                myMergeRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                myMergeRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;

                // when the tab is ready, switch to the next row
                myRange.get_Offset(1, 0).Activate();
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occurred in WriteFormTabHeader() " + ex.Message);
                thisApp.ScreenUpdating = true;
            }
        }

        private void WriteFormFormFooter(Excel.Application thisApp)
        {
            try
            {
                //create the Footer 
                Excel.Range myRange = thisApp.ActiveCell;

                myRange.Value2 = " ";
                myRange.Font.Size = 14;
                Excel.Range myMergeRange = thisApp.get_Range(thisApp.ActiveCell, thisApp.ActiveCell.get_Offset(0, 4));
                myMergeRange.Merge(missing);
                myRange.Font.Bold = true;

                myRange.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
                myRange.Interior.PatternColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
                myRange.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorLight1;
                myRange.Interior.TintAndShade = 0.599993896298105;
                myRange.Interior.PatternTintAndShade = 0;

                myMergeRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                myMergeRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                myMergeRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                myMergeRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
                myRange.get_Offset(1, 0).Activate();
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occurred in WriteFormFormFooter() " + ex.Message);
                thisApp.ScreenUpdating = true;
            }
        }

        public static string Left(string text, int length)
        {
            if (length < 0)
                throw new ArgumentOutOfRangeException("length", length, "length must be > 0");
            else if (length == 0 || text.Length == 0)
                return "";
            else if (text.Length <= length)
                return text;
            else
                return text.Substring(0, length);
        }


    }
}
